import sqlexecx as db
import importlib
from config import MYSQL
from threading import Thread


def full_test():
    print(db.save('user', name='zhangsan', age=54))
    print(db.save_sql('INSERT INTO user(name,age) VALUES(?,?)', 'lisi', 26))
    print(db.save_select_key("SELECT LAST_INSERT_ID()", 'user', name='zhangsan', age=54))
    print(db.save_sql_select_key("SELECT LAST_INSERT_ID()", 'INSERT INTO user(name,age) VALUES(?,?)', 'lisi', 26))
    print(db.insert(table_name='user', name='wangwu', age=38))

    args = [
        {'name': '李四', 'age': 55, 'birth_date': '1968-10-08', 'sex': 0, 'grade': 1.0, 'point': 20.5, 'money': 854.56},
        {'name': '李四', 'age': 55, 'birth_date': '1968-10-08', 'sex': 0, 'grade': 1.0, 'point': 20.5, 'money': 854.56}
    ]
    db.batch_insert('user', *args)

    print(db.select('select * from user where name in(?,?)', 'zhangsan', 'lisi'))
    print(db.select_one('select * from user where name = ? limit 1', 'zhangsan'))

    print(db.query('select id, name, age from user'))
    print(db.query_one('select id, name, age from user where name=?', 'zhangsan'))

    print(db.get('select count(1) from user'))

    # print(db.select_page('select id, name, age from person limit ?,?', 1, 2))
    # print(db.query_page('select id, name, age from person', 1, 2))

    print(db.execute('delete from user where id = ?', 3))


if __name__ == '__main__':
    # from mysql.connector import connect
    db.init(**MYSQL)
    # db.execute('truncate table user')
    db.truncate_table('user')
    print('after truncate count:', db.table('user').count())

    print(db.sql('select * from user').select_one())

    for _ in range(2):
        t = Thread(target=full_test()).start()

    # db.sql('select id, name, age from user').csv().save('tt.csv', delimiter='|', header=False)
    # df = db.sql('select id, name, age from user').load().df()
    # print(df)

    # df = db.table('user').df('id', 'name', 'age')
    # print(df)
    cols = ('id', 'name', 'age', 'birth_date', 'sex', 'grade', 'point', 'money', 'create_time', 'del_flag')
    # db.table('user').load(*cols).to_csv('tt.csv')
    # db.execute('truncate table user')
    # db.insert_from_csv('tt.csv', 'user')
    # db.table('user').insert_from_csv('tt.csv', columns=cols)

    # df = db.table('user').load(*cols).to_df()
    # print(df)
    # db.execute('truncate table user')
    # db.table('user').insert_from_df(df)

    # db.table('user').load(*cols).to_json('tt.json')
    # db.execute('truncate table user')
    # db.insert_from_json('tt.json', 'user')

    df = db.table('user').load(*cols).to_df()
    print(df)

    data = db.sql('select id, name, age from user where name = :name').param(name='张三').select()
    for d in data:
        print(d)

    db.sql('select id, name, age from user where name = :name').param(name='张三').to_csv('tt.csv')
    db.sql('select id, name, age from user where name = :name').param(name='张三').to_json('tt.json')

    args = [
        ('addd', 55, '1968-10-08', 0, 1.0, 20.5, 854.56),
        ('bbb', 55, '1968-10-08', 0, 1.0, 20.5, 854.56)
    ]
    # db.table('user').columns('name', 'age', 'birth_date', 'sex', 'grade', 'point', 'money').insert(*args[0])
    db.table('user').columns('name', 'age', 'birth_date', 'sex', 'grade', 'point', 'money').batch_insert(*args)

    df = db.table('user').columns(*cols).to_df()
    print(df)

    name = db.table('user').columns('count(1)').where(name__eq='张三').get()
    print(name)

    db.close()
    